<?php 
	require_once("connection.php");//Require connection function
    require_once($_SERVER['DOCUMENT_ROOT']."/configures/global.php");//Import global function/const
    require_once("helper.php");
    function update_city($cid,$country,$city,$lan,$conn=DB)
    {
        /**
        *   Function name   Purpose 
        *   update_city     Update specific city record name and country.
        *   
        *   params     description
        *   $cid       Cities id . 
        *   $country   New country name
        *   $city      New city name
        *   $lan       Languages to update (cn or en)
        *   $conn      PDO connection instance
        **/
        
        $sql_str = "UPDATE `Cities` SET CityName_{$lan} = :c , CountryName_{$lan} = :cc ";
        $sql_str.= " WHERE `CityID` = :cid";
        $conn = create_connection($conn);
        $stmt = $conn->prepare($sql_str);
        $stmt->bindValue(":c",$city,PDO::PARAM_STR);
        $stmt->bindValue(":cc",$country,PDO::PARAM_STR);
        $stmt->bindValue(":cid",$cid,PDO::PARAM_STR);
        $stmt->execute();
    }
    function create_city($country,$city,$lan,$conn=DB)
    {
        /**
        *   Function name   Purpose 
        *   create_city     Create city record .
        *   
        *   params     description
        *   $country   New country name
        *   $city      New city name
        *   $lan       Languages to update (cn or en)
        *   $conn      PDO connection instance
        **/       
        
        $sql_str = "INSERT INTO `Cities` (CityName_{$lan}, CountryName_{$lan})";
        $sql_str.= " VALUES(:c,:cc) ";
        $conn = create_connection($conn);
        $stmt = $conn->prepare($sql_str);
        $stmt->bindValue(":c",$city,PDO::PARAM_STR);
        $stmt->bindValue(":cc",$country,PDO::PARAM_STR);
        $stmt->execute();
        return $conn->lastInsertId();
    }
    function get_city_id($country,$city,$conn=DB)
    {
        /**
        *   Function name   Purpose 
        *   get_city_id     Create city record .
        *   
        *   params     description
        *   $country   New country name
        *   $city      New city name
        *   $lan       Languages to update (cn or en)
        *   $conn      PDO connection instance
        *   
        *   Return city id of specific country and city , if country,city not found , it will return -1
        **/ 
        
        $sql_str = "SELECT CityID FROM `Cities` ";
        $sql_str.= "WHERE ( `CityName_en` = :c ";
        $sql_str.= " OR `CityName_cn` = :c ";
        $sql_str.= " )  ";
        $sql_str.= " AND ";
        $sql_str.= " ( `CountryName_en` = :co ";
        $sql_str.= "OR `CountryName_cn` = :co ) ";
        $conn = create_connection($conn);
        $stmt = $conn->prepare($sql_str);
        $stmt->bindValue(":c",$city,PDO::PARAM_STR);
        $stmt->bindValue(":co",$country,PDO::PARAM_STR);
        $stmt->execute();
        $cityId = -1;//Default city id , city id < 0 is meaning city not found.
        while ($row = $stmt->fetch())
        {
            $cityId = $row["CityID"];//If sql return more than one , city id will be updated .
        }
        return $cityId;
    }
	function bind_params($stat,$params)
	{
        /**
        *   Function name   Purpose
        *   bind_params     Wrapping PDO function bind_value since statement object must be initial before you can bind the value . 
        *                   In case we may dynamic change number of bind value according to some parameter if keep using PDO bind_value function we may repeat our conditions two times.
        *   Params  Description
        *   $stat   Statement object created by PDO connection
        *   $params Associative array of parameter , key should be name of parameter , value should be array , 0 is value , 1 is type
        *           For available type you may referring to http://php.net/manual/en/pdo.constants.php (Most time i only using PARAM_STR and PARAM_INT)
        **/
		foreach ($params as $key=>$param)
		{
			$stat-> bindValue($key,$param[0],$param[1]);
		}
	}
    function get_all_locations($lan=DEFAULT_LN,$map_function=NULL)
    {
        /**
        *   Function name       Purpose
        *   get_all_locations   Select all locations that contain at least one entity
        *   
        *   Params          Description
        *   $lan            Result language (en or cn)
        *   $map_function   function for post processing
        *   Return all record of categoryview
        **/
        $sql_str = "SELECT * FROM categoryview";
        
    }
	function get_continents($lan=DEFAULT_LN)
	{
        /**
        *   Function name   Purpose
        *   get_continents   Select all continent name in database
        *   
        *   Params          Description
        *   $lan            Result language (en or cn)
        *   
        *   Return all name of continent
        **/
		
		$sql_str = "SELECT DISTINCT ContinentName_{$lan} AS ContinentName FROM `categoryview`";
        $conn = create_connection();
		$stmt = $conn->prepare($sql_str);
		$stmt->execute();
		return $stmt->fetchAll();
	}
	function get_countries($continent,$lan=DEFAULT_LN)
	{
        /**
        *   Function name                   Purpose
        *   get_countries                   Return all countries inside continent
        *   
        *   Params                          Description
        *   $continent                      Target continent
        *   $lan                            Languages to extract
        **/
		
		$sql_str = "SELECT DISTINCT c.`CountryName_{$lan}` AS CountryName FROM `categoryview` AS c  WHERE  c.`ContinentName_cn` = :c_name";
		$sql_str.= " OR c.`ContinentName_en` = :c_name";
		$sql_str.= " OR c.`ContinentName_pinyin` = :c_name";
        $conn = create_connection();
		$stmt = $conn->prepare($sql_str);
		$stmt->bindValue(":c_name",$continent,PDO::PARAM_STR);
		$stmt->execute();
		return $stmt->fetchAll();
	}
	function get_cities($continent,$country,$lan=DEFAULT_LN)
	{
        /**
        *   Function name                   Purpose
        *   get_cities                      Return all countries inside continent and country
        *   
        *   Params                          Description
        *   $continent                      Target continent
        *   $country                        Target country
        *   $lan                            Languages to extract
        **/
		
		$sql_str = "SELECT DISTINCT c.`CityName_{$lan}` AS CityName FROM `categoryview` AS c  WHERE ";
		$sql_str.="( c.`ContinentName_en` = :c_name";
		$sql_str.=" OR c.`ContinentName_cn` = :c_name";
		$sql_str.="OR c.`ContinentName_pinyin` = :c_name )";
		$sql_str.=" AND ";
		$sql_str.="(c.`CountryName_en` = :cc_name";
		$sql_str.=" OR c.`CountryName_cn` = :cc_name";
		$sql_str.="OR c.`CountryName_pinyin` = :cc_name )";
        $conn = create_connection();
		$stmt = $conn->prepare($sql_str);
        $stmt->bindValue(":c_name",$continent,PDO::PARAM_STR);
		$stmt->bindValue(":cc_name",$country,PDO::PARAM_STR);
		$stmt->execute();
		return $stmt->fetchAll();
	}
    /*Query location by regular , it much flexibility than Like */
    function search_continents($continent_reg,$cate=NULL,$map_function=NULL)
    {
        /**
        *   Function name                   Purpose
        *   search_continents               According continent regex pattern ,return matched location.
        *   
        *   Params                          Description
        *   $continent_reg                  Regex pattern for continent , [\w ]* for anything
        *   $cate                           Categories instance to indicate what category to query
        *   $msp_function                   Post process function for query result . see array_map function for this function spec.
        *                                   http://www.w3schools.com/php/func_array_map.asp
        **/

        $params = [];
		$sql_str = "SELECT * FROM `Categoryview` AS c WHERE " ;
		$sql_str.= " (c.`ContinentName_en` REGEXP :c_name";
		$sql_str.= " OR c.`ContinentName_cn` REGEXP :c_name";
		$sql_str.= " OR c.`ContinentName_pinyin` REGEXP :c_name)";
        $params[":c_name"] = [$continent_reg,PDO::PARAM_STR];
        if (is_null($cate))
        {
            $cate = new Categories();
        }
        if (!$cate->is_all() )
        {
            $sql_str.= " AND ";
            $sql_str .= " c.`CategoryID` = :cateId";
            $params[":cateId"] = [$cate->getCurrentCategorieID(),PDO::PARAM_INT];
        }
        $conn = create_connection();
		$stmt = $conn->prepare($sql_str);
        bind_params($stmt,$params);
		$stmt->execute();
		return is_null($map_function)?$stmt->fetchAll():array_map($map_function,$stmt->fetchAll());
    }

	function search_countries($country_reg,$continent_reg=NULL,$cate=NULL,$map_function=NULL)
	{
        /**
        *   Function name                   Purpose
        *   search_countries                According country , continent regex pattern ,return matched location.
        *   
        *   Params                          Description
        *   $country_reg                    Regex pattern for country , [\w ]* for anything
        *   $continent_reg                  Regex pattern for continent , [\w ]* for anything
        *   $cate                           Categories instance to indicate what category to query
        *   $msp_function                   Post process function for query result . see array_map function for this function spec.
        *                                   http://www.w3schools.com/php/func_array_map.asp
        **/
	    $params = array();
		$sql_str = "SELECT c.* FROM `Categoryview` AS c WHERE "  ;
		$sql_str.= " (c.`CountryName_en` REGEXP :cc_name";
		$sql_str.= " OR c.`CountryName_cn` REGEXP :cc_name";
		$sql_str.= " OR c.`CountryName_pinyin` REGEXP :cc_name) ";
		$params[":cc_name"] = [$country_reg,PDO::PARAM_STR];
		if (!is_null($continent_reg))
		{
			$sql_str .= " AND ( "  ;
			$sql_str.= "c.`ContinentName_en` REGEXP :c_name";
			$sql_str.= " OR c.`ContinentName_cn` REGEXP :c_name";
			$sql_str.= " OR c.`ContinentName_pinyin` REGEXP :c_name ) ";
			$params[":c_name"] = [$continent_reg,PDO::PARAM_STR];
		}
        if (is_null($cate))
        {
            $cate = new Categories();
        }
        if (!$cate->is_all() )
        {
            $sql_str.= " AND ";
            $sql_str .= " c.`CategoryID` = :cateId";
            $params[":cateId"] = [$cate->getCurrentCategorieID(),PDO::PARAM_INT];
        }

        $conn = create_connection();
		$stmt = $conn->prepare($sql_str);
		bind_params($stmt,$params);
		$stmt->execute();
		return is_null($map_function)?$stmt->fetchAll():array_map($map_function,$stmt->fetchAll());
	}
	function search_cities($city_reg=NULL,$country_reg=NULL,$continent_reg=NULL,$cate=NULL,$map_function=NULL)
	{
	    /**
        *   Function name                   Purpose
        *   search_cities                   According city , country , continent regex pattern ,return matched location.
        *   
        *   Params                          Description
        *   $city_reg                       Regex pattern for city , [\w]* for anything
        *   $country_reg                    Regex pattern for country , [\w ]* for anything
        *   $continent_reg                  Regex pattern for continent , [\w ]* for anything
        *   $cate                           Categories instance to indicate what category to query
        *   $msp_function                   Post process function for query result . see array_map function for this function spec.
        *                                   http://www.w3schools.com/php/func_array_map.asp
        **/
	    $params = array();
		$sql_str = "SELECT c.* FROM `Categoryview` AS c  ";
        $sql_str.= " WHERE ( c.`CityName_en` REGEXP :ccc_name";
        $sql_str.= " OR c.`CityName_cn` REGEXP :ccc_name";
        $sql_str.= " OR c.`CityName_pinyin` REGEXP :ccc_name )  ";
        $params[":ccc_name"] = [$city_reg,PDO::PARAM_STR];
        if (!is_null($country_reg))
		{
			$sql_str.= " AND (c.`CountryName_en` REGEXP :cc_name";
			$sql_str.= " OR c.`CountryName_cn` REGEXP :cc_name";
			$sql_str.= " OR c.`CountryName_pinyin` REGEXP :cc_name) ";
			$params[":cc_name"] = [$country_reg,PDO::PARAM_STR];
		}
		if (!is_null($continent_reg))
		{
			$sql_str .= " AND ( "  ;
			$sql_str.= "c.`ContinentName_en` REGEXP :c_name";
			$sql_str.= " OR c.`ContinentName_cn` REGEXP :c_name";
			$sql_str.= " OR c.`ContinentName_pinyin` REGEXP :c_name ) ";
			$params[":c_name"] = [$continent_reg,PDO::PARAM_STR];
		}
        if (is_null($cate))
        {
            $cate = new Categories();
        }
        if (!$cate->is_all() )
        {
            $sql_str.= " AND ";
            $sql_str .= " c.`CategoryID` = :cateId";
            $params[":cateId"] = [$cate->getCurrentCategorieID(),PDO::PARAM_INT];
        }
        $conn = create_connection();
		$stmt = $conn->prepare($sql_str);
		bind_params($stmt,$params);
		$stmt->execute();
		return is_null($map_function)?$stmt->fetchAll():array_map($map_function,$stmt->fetchAll());
	}
    /*End query function*/
    function get_query_records_count($query,$params)
    {
	    /**
        *   Function name                   Purpose
        *   get_query_records_count         Count total number of records on query 
        *   
        *   Params                          Description
        *   $query                          SQL query after select statement , for instance , If your sql is SELECT * FROM ABC a WHERE a.id > 20; , $query should be " FROM ABC a WHERE a.id > 20"
        *   $params                         Parameters of your query
        **/
        $sql_str= "SELECT COUNT(*) AS RECORD_COUNT ";
        $sql_str .= $query;
        $conn = create_connection();
        $stmt = $conn->prepare($sql_str);
		bind_params($stmt,$params);
		$stmt->execute();
        return $stmt->fetch()["RECORD_COUNT"];
    }
    //Alway return one hotel record.
    function get_hotel($hotel_uuid,$lan)
    {
	    /**
        *   Function name                   Purpose
        *   get_hotel                       Return one hotels record according to WC UUID;
        *   
        *   Params                          Description
        *   $hotel_uuid                     Hotel B2B UUID 
        *   $lan                            Languages to return
        **/   
		$params = array();
        $sql_str = "SELECT h.*,ce.*,c.*,cs.* , elan.* , hlan.* ";
        $sql_str .= " FROM `hotels` AS h ,`customers` AS cs,`customerentity` AS ce ,  `cities` AS c , `entity_{$lan}` AS elan , `hotels_{$lan}` AS hlan ";
		$sql_str .= " WHERE h.`EntityID` = ce.`EntityID` ";
		$sql_str .= " AND ce.`CityID` = c.`CityID` ";
        $sql_str .= " AND h.`HotelWCUUID` = :hotel_uuid ";
        $sql_str .= " AND ce.`CustomerID` = cs.`CustomerID` ";
        $sql_str .= " AND elan.`EntityID` = ce.`EntityID` ";
        $sql_str .= " AND hlan.`HotelID` = h.`HotelID` ";
        $params[":hotel_uuid"] = [$hotel_uuid , PDO::PARAM_INT];
        $conn = create_connection();
        $stmt = $conn->prepare($sql_str);
		bind_params($stmt,$params);
		$stmt->execute();
		return $stmt->fetch();
    }
    //Page > 0 = enable , hotel_count is reference of variable (pass by reference / pass by address rather than pass by value ) and it will set to total number of record 
    function get_hotels($city=NULL,$country=NULL,$continent=NULL,$rating=NULL,$service=-1,$map_function=NULL,$page=-1,$lan=DEFAULT_LN,&$record_count=NULL,&$rating_group=NULL,&$service_group=NULL)
    {
		/**
        *   Function name                   Purpose
        *   get_hotels                      Return hotels that full fill given condition(s) 
        *   
        *   Params                          Description
        *   $city_reg                       City name
        *   $country_reg                    Country name
        *   $continent_reg                  Continent name
        *   $rating                         Hotel rating filter , it should array , for instance , select all 5 star hotel should be [5] , all 5 or 3 stars should [3,5] and so on .
        *   $service                        Hotel service filter , integer . services is constructed by 9 bits . 
                                            0 for no this service , 1 for yes and each position of bit meaning is listed below:
                                            0 (2^0) = 中央电视台频道
                                            1 (2^1) = 优选中文电视频道
                                            2 (2^2) = <<欢迎中国>> 杂志 (2015年开始)
                                            3 (2^3) = 银联支付
                                            4 (2^4) = 热水壶和烧水器
                                            5 (2^5) = 提供普通话服务
                                            6 (2^6) = 供应中式早餐
                                            7 (2^7) = 为“欢迎中国”持卡人提供的特别折扣 (2015年开始)
                                            8 (2^8) = 欢迎中国礼包
        *   $msp_function                   Post process function for query result . see array_map function for this function spec. NULL for nothing post process
        *                                   http://www.w3schools.com/php/func_array_map.asp
        *   $page                           If page > 0 , server side pagination will enabled . result will only part of records. 
        *   $lan                            Languages to return
        *   &$record_count                  If pagination is enabled , this can not be null , a variable reference for hold total number of record
        *   &$rating_group                  If not null , it should a variable reference for hold how many matched hotel in each group of rating . (How many hotel is 5 star , how many is 4 star...etc.)
        *   &$service_group                 Similar to rating_group , but it is group by service . 
        **/   
		$params = array();
        $select = "SELECT h.*,ce.*,cs.* , elan.* , hlan.* ";
        $sql_str = " FROM `hotels` AS h ,`customerentity` AS ce,`customers` AS cs ,  `cities` AS c  , `entity_{$lan}` AS elan , `hotels_{$lan}` AS hlan ";
		$sql_str .= " WHERE h.`EntityID` = ce.`EntityID` ";
		$sql_str .= " AND ce.`CustomerID` = cs.`CustomerID` ";
		$sql_str .= " AND ce.`CityID` = c.`CityID` ";
        $sql_str .= " AND ce.`TypeCategory` = 1 " ;
        $sql_str .= " AND ce.`TypeSubcategory` = 'HOTEL' " ;
        $sql_str .= " AND elan.`EntityID` = ce.`EntityID` ";
        $sql_str .= " AND hlan.`HotelID` = h.`HotelID` ";
		if (!is_null($city))
		{
			$sql_str.= " AND ( c.`CityName_en` = :ccc_name";
			$sql_str.= " OR c.`CityName_cn` = :ccc_name";
			$sql_str.= " OR c.`CityName_pinyin` = :ccc_name ) ";
			$params[":ccc_name"] = [$city,PDO::PARAM_STR]  ;
		}
		if (!is_null($country))
		{
			$sql_str.= " AND (c.`CountryName_en` = :cc_name";
			$sql_str.= " OR c.`CountryName_cn` = :cc_name";
			$sql_str.= " OR c.`CountryName_pinyin` = :cc_name) ";
			$params[":cc_name"] = [$country,PDO::PARAM_STR] ;
		}
		if (!is_null($continent))
		{
			$sql_str .= " AND ( "  ;
			$sql_str.= "c.`ContinentName_en` = :c_name";
			$sql_str.= " OR c.`ContinentName_cn` = :c_name";
			$sql_str.= " OR c.`ContinentName_pinyin` = :c_name ) ";
			$params[":c_name"] = [$continent,PDO::PARAM_STR]  ;
		}
        if (!is_null($rating))
        {
        	$sql_str .= " AND ( "  ;
            foreach ($rating as $key=>$rate)
            {
                $sql_str.= " h.`HotelRating` = :rating_{$key} ";
                if ($key != (count($rating) - 1))
                {
                    $sql_str.= " OR ";
                }
			$params[":rating_{$key}"] = [$rate,PDO::PARAM_INT]  ;
            }
            $sql_str .= "  ) "  ;
			
        }
        if ($service >=0)
        {
        	$sql_str .= " AND  "  ;
			$sql_str.= " ((h.`HotelService` & :service) = :service)";
			$params[":service"] = [$service,PDO::PARAM_INT]  ;
        }
        $sql_str .= " ORDER BY cs.`CertLevel` DESC , ce.`CNTClick` DESC ,h.`HotelService` DESC , h.`HotelRating` DESC " ;
        if ($page > 0)
        {
            $record_count = get_query_records_count($sql_str,$params);//Count number of record 
            $sql_str.= " LIMIT :start_offset,:end_offset ";//Pagination
            $params[":start_offset"] = [($page-1) * PAGE_SIZE,PDO::PARAM_INT]  ;
            $params[":end_offset"] = [PAGE_SIZE ,PDO::PARAM_INT]  ;
        }
        $conn = create_connection();
		$stmt = $conn->prepare($select . $sql_str);
		bind_params($stmt,$params);
		$stmt->execute();
        $rating_group = get_rating_count($city,$country,$continent,$service);
        $service_group = get_services_count($city,$country,$continent,$rating,$service);
		return is_null($map_function)?$stmt->fetchAll():array_map($map_function,$stmt->fetchAll());
    }
    function get_services_count($city=NULL,$country=NULL,$continent=NULL,$ratings=NULL,$services = -1)
    {
	    /**
        *   Function name                   Purpose
        *   get_services_count              Return array that indicate how many hotel have provide service . ["1" > 255] , 255 hotels have provide 优选中文电视频道 service
        *   
        *   Params                          Description
        *   $city                           City name
        *   $country                        Country name
        *   $continent                      Continent name
        *   $ratings                        Ratings that must match that mean counting will also request hotel is matching rating .  
        *   $service                        Service must match that mean service is must have 
        **/   
        $result = [];
        for ($i = 0;$i < 9;$i++)
        {
            $result[pow(2,$i)] = get_service_count(pow(2,$i),$city,$country,$continent,$ratings,$services);
        }
        return $result;
    }
    function get_service_count($service,$city=NULL,$country=NULL,$continent=NULL,$ratings=NULL,$services = -1)
    {
	    /**
        *   Function name                   Purpose
        *   get_services_count              Return array that indicate how many hotel have provide service . ["1" > 255] , 255 hotels have provide 优选中文电视频道 service
        *   
        *   Params                          Description
        *   $service                        Service you want to count
        *   $city                           City name
        *   $country                        Country name
        *   $continent                      Continent name
        *   $ratings                        Ratings that must match that mean counting will also request hotel is matching rating .  
        *   $services                       Service must match that mean service is must have 
        **/  
		$params = array();
        $sql_str = "SELECT COUNT(*) AS service_count ";
        $sql_str .= " FROM `hotels` AS h ,`customerentity` AS ce ,  `cities` AS c ";
		$sql_str .= " WHERE h.`EntityID` = ce.`EntityID` ";
		$sql_str .= " AND ce.`CityID` = c.`CityID` ";
        $sql_str .= " AND ce.`TypeCategory` = 1 " ;
        $sql_str .= " AND ce.`TypeSubcategory` = 'HOTEL' " ;
		if (!is_null($city))
		{
			$sql_str.= " AND ( c.`CityName_en` = :ccc_name";
			$sql_str.= " OR c.`CityName_cn` = :ccc_name";
			$sql_str.= " OR c.`CityName_pinyin` = :ccc_name ) ";
			$params[":ccc_name"] = [$city,PDO::PARAM_STR]  ;
		}
		if (!is_null($country))
		{
			$sql_str.= " AND (c.`CountryName_en` = :cc_name";
			$sql_str.= " OR c.`CountryName_cn` = :cc_name";
			$sql_str.= " OR c.`CountryName_pinyin` = :cc_name) ";
			$params[":cc_name"] = [$country ,PDO::PARAM_STR] ;
		}
		if (!is_null($continent))
		{
			$sql_str .= " AND ( "  ;
			$sql_str.= "c.`ContinentName_en` = :c_name";
			$sql_str.= " OR c.`ContinentName_cn` = :c_name";
			$sql_str.= " OR c.`ContinentName_pinyin` = :c_name ) ";
			$params[":c_name"] = [$continent,PDO::PARAM_STR]  ;
		}
        if (!is_null($ratings))
        {
        	$sql_str .= " AND ( "  ;
            foreach ($ratings as $key=>$rate)
            {
                $sql_str.= " h.`HotelRating` = :rating_{$key} ";
                if ($key != (count($ratings) - 1))
                {
                    $sql_str.= " OR ";
                }
			$params[":rating_{$key}"] = [$rate,PDO::PARAM_INT]  ;
            }
            $sql_str .= "  ) "  ;
        }
        $sql_str .= " AND  "  ;
        $sql_str.= " ((h.`HotelService` & :service) = :service)";
        if ($services > 0 )
        {
            if (($services & $service) == 0) // current counting service and must have service do not have overlap
            {
                $service = $service + $services;
            }
            else //Must have service and current counting service is overlapped 
            {
                $service = $services;
            }
        }
		$params[":service"] = [$service,PDO::PARAM_INT]  ;
        $conn = create_connection();
		$stmt = $conn->prepare($sql_str);
		bind_params($stmt,$params);
		$stmt->execute();
        return $stmt->fetchAll()[0]["service_count"];   
    }
    function get_rating_count($city=NULL,$country=NULL,$continent=NULL,$services = -1)
    {
	    /**
        *   Function name                   Purpose
        *   get_rating_count                Return array that indicate how many hotel inside rating . ["1" => 255] , 255 hotels is 1 star.
        *   
        *   Params                          Description
        *   $service                        Service you want to count
        *   $city                           City name
        *   $country                        Country name
        *   $continent                      Continent name
        *   $services                       Service must match that mean service is must have 
        **/         
		$params = array();
        $sql_str = "SELECT h.HotelRating , COUNT(*) AS rating_count ";
        $sql_str .= " FROM `hotels` AS h ,`customerentity` AS ce ,  `cities` AS c ";
		$sql_str .= " WHERE h.`EntityID` = ce.`EntityID` ";
		$sql_str .= " AND ce.`CityID` = c.`CityID` ";
        $sql_str .= " AND ce.`TypeCategory` = 1 " ;
        $sql_str .= " AND ce.`TypeSubcategory` = 'HOTEL' " ;
		if (!is_null($city))
		{
			$sql_str.= " AND ( c.`CityName_en` = :ccc_name";
			$sql_str.= " OR c.`CityName_cn` = :ccc_name";
			$sql_str.= " OR c.`CityName_pinyin` = :ccc_name ) ";
			$params[":ccc_name"] = [$city,PDO::PARAM_STR]  ;
		}
		if (!is_null($country))
		{
			$sql_str.= " AND (c.`CountryName_en` = :cc_name";
			$sql_str.= " OR c.`CountryName_cn` = :cc_name";
			$sql_str.= " OR c.`CountryName_pinyin` = :cc_name) ";
			$params[":cc_name"] = [$country ,PDO::PARAM_STR] ;
		}
		if (!is_null($continent))
		{
			$sql_str .= " AND ( "  ;
			$sql_str.= "c.`ContinentName_en` = :c_name";
			$sql_str.= " OR c.`ContinentName_cn` = :c_name";
			$sql_str.= " OR c.`ContinentName_pinyin` = :c_name ) ";
			$params[":c_name"] = [$continent,PDO::PARAM_STR]  ;
		}
        if ($services > 0)
        {
            $sql_str .= " AND  "  ;
			$sql_str.= " (h.`HotelService` & :service )= :service ";
            $params[":service"] = [$services,PDO::PARAM_INT]  ;
        }
        $sql_str.= "GROUP BY h.HotelRating ";
        $sql_str.= "ORDER BY h.HotelRating ";
        $conn = create_connection();
		$stmt = $conn->prepare($sql_str);
		bind_params($stmt,$params);
		$stmt->execute();
        $result = [0,0,0,0,0];
        foreach ($stmt->fetchAll() AS $key=>$value)
        {
            $result[((int)$value["HotelRating"]) - 1] = $value["rating_count"];
        }
        return $result;
    }
    function count_categories_entity($city_reg=NULL,$country_reg=NULL,$continent_reg=NULL,$reduce_function=NULL)
    {
	    /**
        *   Function name                   Purpose
        *   count_categories_entity         Count how many entity inside each category
        *   
        *   Params                          Description
        *   $city_reg                       Regex pattern for city , [\w]* for anything
        *   $country_reg                    Regex pattern for country , [\w]* for anything
        *   $continent_reg                  Regex pattern for continent , [\w]* for anything
        *   $reduce_function                Function that used for post processing , it should reduce function.
        *                                   See http://php.net/manual/en/function.array-reduce.php
        **/       
        $result = [];
        $result["accommodation"]=$result[0]=count_category_entity(new Categories(Categories::ACCOMMODATION),$city_reg,$country_reg,$continent_reg,$reduce_function);
        $result["attraction"]=$result[1]=count_category_entity(new Categories(Categories::TRANSPORT),$city_reg,$country_reg,$continent_reg,$reduce_function);
        $result["food_beverage"]=$result[2]=count_category_entity(new Categories(Categories::SHOPPING),$city_reg,$country_reg,$continent_reg,$reduce_function);
        $result["shopping"]=$result[3]=count_category_entity(new Categories(Categories::ATTRACTION),$city_reg,$country_reg,$continent_reg,$reduce_function);
        $result["transport"]=$result[4]=count_category_entity(new Categories(Categories::FOOD),$city_reg,$country_reg,$continent_reg,$reduce_function);
        return $result;
    }
    /*Except sub category , all should regular expression , check mysql manual to regular expression syntax
    */
    function count_category_entity($catego,$city_reg=NULL,$country_reg=NULL,$continent_reg=NULL,$reduce_function=NULL,$sub_category=NULL)
    {
	    /**
        *   Function name                   Purpose
        *   count_category_entity           Count how many entity inside category
        *   
        *   Params                          Description
        *   $catego                         Categories instance to indicate what type of category
        *   $city_reg                       Regex pattern for city , [\w]* for anything
        *   $country_reg                    Regex pattern for country , [\w]* for anything
        *   $continent_reg                  Regex pattern for continent , [\w]* for anything
        *   $reduce_function                Function that used for post processing , it should reduce function.
        *                                   See http://php.net/manual/en/function.array-reduce.php
        *   $sub_category                   Currently only support "HOTEL" , but that may expand latter time (07/08/2014)
        **/            
		$params = array();
        $sql_str = "SELECT category_view.`CNTEntity` FROM `Categoryview` AS category_view ";
        $sql_str.= "WHERE category_view.`CategoryID` = :categoID";
        $params[":categoID"] = [$catego->getCurrentCategorieID(),PDO::PARAM_INT]  ;   
		if (!is_null($city_reg))
		{
			$sql_str.= " AND ( category_view.`CityName_en` REGEXP :ccc_name";
			$sql_str.= " OR category_view.`CityName_cn` REGEXP :ccc_name";
			$sql_str.= " OR category_view.`CityName_pinyin` REGEXP :ccc_name ) ";
			$params[":ccc_name"] = [$city_reg,PDO::PARAM_STR]  ;
		}
		if (!is_null($country_reg))
		{
			$sql_str.= " AND (category_view.`CountryName_en` REGEXP :cc_name";
			$sql_str.= " OR category_view.`CountryName_cn` REGEXP :cc_name";
			$sql_str.= " OR category_view.`CountryName_pinyin` REGEXP :cc_name) ";
			$params[":cc_name"] = [$country_reg ,PDO::PARAM_STR] ;
		}
		if (!is_null($continent_reg))
		{
			$sql_str.= " AND ( "  ;
			$sql_str.= "category_view.`ContinentName_en` REGEXP :c_name";
			$sql_str.= " OR category_view.`ContinentName_cn` REGEXP :c_name";
			$sql_str.= " OR category_view.`ContinentName_pinyin` REGEXP :c_name ) ";
			$params[":c_name"] = [$continent_reg,PDO::PARAM_STR]  ;
		}         
        if (!is_null($sub_category))
		{
			$sql_str.= " AND  "  ;
			$sql_str.= "category_view.`TypeSubcategory` = :sub_category";
			$params[":sub_category"] = [$sub_category,PDO::PARAM_STR]  ;
		}
        $conn = create_connection();
        $stmt = $conn->prepare($sql_str);
		bind_params($stmt,$params);
		$stmt->execute();
		return is_null($reduce_function)?$stmt->fetchAll():array_reduce($stmt->fetchAll(),$reduce_function,0);
    }
?>